package pkg3hge_plh24;

import java.sql.*;
import java.util.ArrayList;
import exeptions.ControlledUserException;


public class DBManager {
  // Πεδία

    // Σταθερές οι οποίες έχουν ως τιμές τις απαραίτητες πληροφορίες
    // για την σύνδεση με την Βάση Δεδομένων

       private final String dbPath = "DB\\petros";
       private final String user = "user";
       private final String pass = "test";
       private final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
       private final String connString = "jdbc:derby:";
       private Connection dBconn;
       private Statement statement;


         //Ερωτήματα Επιλογήσς
      private PreparedStatement selectAreaName ;      
      private PreparedStatement selectAreaId;         
      private PreparedStatement selectCatName;        
      private PreparedStatement selectCategoryId;          
      private PreparedStatement selectPRName;         
      private PreparedStatement selectPRId;           
      private PreparedStatement selectRestoraunt;     
      private PreparedStatement selectRestorauntId;   
      private PreparedStatement selectUsers;          
      private PreparedStatement selectEvalauation;    
    //Ερωτήματα Εισαγωγήσ

      private PreparedStatement insertRestoraunt;       
      private PreparedStatement updateRestoraunt;       
      private PreparedStatement insertEvalauation;     
      private PreparedStatement insertArea;
      private PreparedStatement insertCategory;
	//Ερωτήματα Διαγραφήσ

      private PreparedStatement deleteRestoraunt;       
      private PreparedStatement deleteEvalauation;      

	/*
    private Statement RestorauntSearch;

    // SQL queries που αφορούν τον πίνακα XRHSTES


    // SQL queries που αφορούν τον πίνακα AXIOLOGHSH

    private Statement EvalauationNextId;
    private PreparedStatement EvalauationAvgRatings;
 */

    public  DBManager ()throws SQLException, ClassNotFoundException {
      //  try{
              // Φόρτωμα της κλάσης του οδηγού της DataBase
        Class.forName(driver);

        // Σύνδεση με την βάση rest
        dBconn = DriverManager.getConnection(connString+dbPath, user, pass);

        // Αρχικοποίηση των PreparedStatements
        selectAreaName = dBconn.prepareStatement("SELECT * FROM APP.AREA WHERE AREAID = ?");
        selectAreaId= dBconn.prepareStatement("SELECT AREAID FROM APP.AREA");
        selectCatName = dBconn.prepareStatement("SELECT * FROM APP.CATEGORY WHERE CATEGORYID = ?");
        selectCategoryId = dBconn.prepareStatement("SELECT CATEGORYID FROM APP.CATEGORY");
        selectPRName=dBconn.prepareStatement("SELECT * FROM APP.PRICE_RANGE WHERE PRID = ?");
        selectPRId=dBconn.prepareStatement("SELECT PRID FROM APP.PRICE_RANGE");
        selectRestoraunt= dBconn.prepareStatement("SELECT RESTAURANTID FROM APP.RESTAURANT");
        selectRestorauntId=dBconn.prepareStatement("SELECT * FROM APP.RESTAURANT WHERE RESTAURANTID = ?");
        selectUsers=dBconn.prepareStatement("SELECT * FROM APP.USERS WHERE USERNAME = ?");
        selectEvalauation=dBconn.prepareStatement("SELECT * FROM APP.EVALUATION WHERE EVID = ?");


        insertRestoraunt=dBconn.prepareStatement("INSERT INTO APP.RESTAURANT VALUES (?,?,?,?,?,?,?,?,?,?)");

        insertEvalauation=dBconn.prepareStatement("INSERT INTO APP.EVALUATION VALUES (?,?,?,?,?,?,?)");
        insertArea=dBconn.prepareStatement("INSERT INTO APP.AREA VALUES (?,?)");
        insertCategory=dBconn.prepareStatement("INSERT INTO APP.CATEGORY VALUES (?,?)");

        updateRestoraunt=dBconn.prepareStatement("UPDATE APP.RESTAURANT SET NAME=?, ADDRESS=?, PHONE=?,"
                                              + " INFORMATION=?, DESCRIPTION=?, FOTO=?, PRID=?, AREAID=?, CATEGORYID=?"
                                              + "WHERE RESTAURANTID = ?");

        deleteRestoraunt=dBconn.prepareStatement("DELETE FROM APP.RESTAURANT WHERE RESTAURANTID = ?");



      

        }
    

      
// Κλείνει την σύνδεση με την βάση
    public void close()throws SQLException{
            
        dBconn.close();
            
    }





  //Μέθοδος που επιστρέφει  μια περιοχή με βάση το areaId
public Area areaNameSelect(int areaId) throws SQLException, ControlledUserException {
         Area ar=null;
   selectAreaName.setInt(1, areaId);
    ResultSet rs = selectAreaName.executeQuery();
   while (rs.next()){
   
        String onoma = rs.getString(2);
        ar = new Area(areaId,onoma);
        }
       return ar;
   }


//Μέθοδος που δημιουργεί ένα ArrayList  με όλες τισ περιοχες που βρίσκονται στην ΒΔ

  public ArrayList<Area> getArea ()throws SQLException, ControlledUserException {
	ArrayList<Area> areas = new ArrayList<Area>();
        ResultSet rs = selectAreaId.executeQuery();
            while (rs.next()){
            int arId = rs.getInt(1);
            areas.add(areaNameSelect(arId));
                        }
        return areas;
    }





  //Μέθοδος που επιστρέφει  μια κατηγορία  με βάση το CATEGORYID
public Category catNameSelect(int categoryId) throws SQLException, ControlledUserException {
         Category cat=null;
   selectCatName.setInt(1, categoryId);
    ResultSet rs = selectCatName.executeQuery();
   while (rs.next()){
  
        String onoma = rs.getString(2);
       cat = new Category (categoryId,onoma);
        }
       return cat;
   }


//Μέθοδος που δημιουργεί ένα ArrayList  με όλες τισ κατηγορίες  που βρίσκονται στην ΒΔ

  public ArrayList<Category> getCategory ()throws SQLException, ControlledUserException {
	ArrayList<Category> categ = new ArrayList<Category>();
        ResultSet rs = selectCategoryId.executeQuery();
            while (rs.next()){
            int arId = rs.getInt(1);
            categ.add(catNameSelect(arId));
                        }
        return categ;
    }




 

  //Μέθοδος που επιστρέφει  μια κατηγορία  με βάση το PRId (το id του εύρους τιμών )
public PriceRange pRNameSelect(int PRId) throws SQLException, ControlledUserException {
         PriceRange pr=null;
  selectPRName.setInt(1,PRId);
    ResultSet rs = selectPRName.executeQuery();
   while (rs.next()){
    
        String onoma = rs.getString(2);
       pr = new PriceRange (PRId,onoma);
        }
       return pr;
   }


//Μέθοδος που δημιουργεί ένα ArrayList  με όλα εύροι τιμών  που βρίσκονται στην ΒΔ

  public ArrayList<PriceRange> getPriceRange()throws SQLException, ControlledUserException {
	ArrayList<PriceRange> pr = new ArrayList <PriceRange>();
        ResultSet rs = selectPRId.executeQuery();
            while (rs.next()){
            int prId = rs.getInt(1);
             pr.add(pRNameSelect (prId));
                        }
        return pr;
    }

 

//Μέθοδος που  επιστέφει όλα τα πεδία ενός εστιατορίου με βάση το  restId του
 public Restaurant restorauntSelect(int restId) throws SQLException, ControlledUserException {
        Restaurant rest = null;

        selectRestoraunt.setInt(1, restId);
          ResultSet rs = selectRestoraunt.executeQuery();
          while (rs.next()){
            String name = rs.getString(2);
            String address = rs.getString(3);
            String phone = rs.getString(4);
            String information = rs.getString(5);
            String photo = rs.getString(6);
            String description = rs.getString(7);
            PriceRange pr= pRNameSelect(rs.getInt(8));
            Category cat = catNameSelect(rs.getInt(9));
            Area area =areaNameSelect(rs.getInt(10));
          rest= new Restaurant (restId, name, address, phone,information, photo, 
                  description, pr,cat,area);
          }
          return rest;
    }





//Μέθοδος που δημιουργεί ένα ArrayList  με όλα τα εστιατόρια που βρίσκονται στην βάση
    public ArrayList<Restaurant> getRestaurant ()throws SQLException, ControlledUserException {
	ArrayList<Restaurant> rests = new ArrayList<Restaurant>();
        ResultSet rs = selectRestoraunt.executeQuery();
            while (rs.next()){
            int estsId = rs.getInt(1);
            rests.add(restorauntSelect(estsId));
            }
        return rests;
    }
 
    



   //Μέθοδος που κάνει εισαγωγή ενός εστιατορίου στην ΒΔ
public void insertRestoraunt(Restaurant rest) throws SQLException, ControlledUserException  {

        insertRestoraunt.setInt(1, rest.getRestId());
        insertRestoraunt.setString(2, rest.getName());
        insertRestoraunt.setString(3, rest.getAddress());
        insertRestoraunt.setString(4, rest.getPhone());
        insertRestoraunt.setString(5, rest.getInformation());
        insertRestoraunt.setString(6, rest.getPhoto());
        insertRestoraunt.setString(7, rest.getDescription());
        insertRestoraunt.setInt(8, rest.getPriceRange().getPRId());
        insertRestoraunt.setInt(9, rest.getCategory().getCategoryId());
        insertRestoraunt.setInt(10, rest.getArea().getAreaId());

        insertRestoraunt.executeUpdate();

}




 //Μέθοδος που κάνει ανανέωση των στοιχείων  ενός εστιατορίου στην ΒΔ
public void updateRestoraunt(Restaurant rest) throws SQLException, ControlledUserException  {

        updateRestoraunt.setInt(1, rest.getRestId());
        updateRestoraunt.setString(2, rest.getName());
        updateRestoraunt.setString(3, rest.getAddress());
        updateRestoraunt.setString(4, rest.getPhone());
        updateRestoraunt.setString(5, rest.getInformation());
        updateRestoraunt.setString(6, rest.getPhoto());
        updateRestoraunt.setString(7, rest.getDescription());
        updateRestoraunt.setInt(8, rest.getPriceRange().getPRId());
        updateRestoraunt.setInt(9, rest.getCategory().getCategoryId());
        updateRestoraunt.setInt(10, rest.getArea().getAreaId());

        updateRestoraunt.executeUpdate();

    }



    //Μέθοδος που κάνει φιαγραφη ενός εστιατορίου απο την ΒΔ
public void deleteRestoraunt(Restaurant rest) throws SQLException, ControlledUserException  {

        deleteRestoraunt.setInt(1, rest.getRestId());
         deleteRestoraunt.executeUpdate();

    }






// ΜΕΘΟΔΟΣ ΓΙΑ ΤΟΝ ΕΛΕΓΧΟ ΕΙΣΟΔΟΥ ΣΤΟ ΠΡΟΓΡΑΜΑ
public Users getUser (String userName, String password )throws SQLException, ControlledUserException
{
       
          Users controlledUser = null;
        
      selectUsers.setString(1, userName);
        ResultSet rs = selectUsers.executeQuery();
        if (rs.next()){
            String pass = rs.getString(2);
                if (!password.equals(pass))
                throw new ControlledUserException("Λαθος username");
                    String name = rs.getString(3);
           String surname = rs.getString(3);
           int stat = rs.getInt(5);
           if (stat == Users.status)
               controlledUser = new Administrator(userName,name,surname);
            else
                controlledUser = new Users(userName,  name, surname);
         }


        return controlledUser;
    }
 }
